package com.saic.parse.impl;

import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import com.saic.parse.IImport;

public class ImportSeriesParseAutoHome implements IImport {
	@Override
	public void importExcel(Connection conn) throws Exception {

		int flag = 0;
		Statement stmt = conn.createStatement();
		stmt.execute("DROP TABLE IF EXISTS `t_temp_subbrand_bitauto_r`;");
		//新的id  根据指定表的下一个增加量
		ResultSet resultSetNew = stmt.executeQuery("SELECT auto_increment FROM information_schema.`TABLES` WHERE TABLE_SCHEMA='"+DB_NAME+"' AND TABLE_NAME='t_autohome_vel_series'");
		resultSetNew.next();
		int newId = resultSetNew.getInt(1);				//车系id新的id
		
		if(flag ==0){
			flag = newId;
		}
		
		String url = null;
		InputStream stream = new FileInputStream("d:/series.xls");  
		HSSFWorkbook wb = new HSSFWorkbook(stream); 
		Sheet sheet = wb.getSheetAt(0);  
		//获取数据
		for(Row row:sheet){
			String a_brand_webid = null;		// 汽车之家主品牌webid 
			String a_brand_name = null;			// 汽车之家主品牌名
			String a_subbrand_webid = null;		// 汽车之家子品牌 webid
			String a_subbrand_name = null;		// 汽车之家子品牌名
			String a_desc = null;				// 描述
			String a_series_webid = null;		// 汽车之家车系webid
			String a_series_name = null;		// 汽车之家车系名
			
			String x_brand_webid = null;		// xcar主品牌webid
			String x_subbrand_webid = null;		// xcar子品牌webid
			String x_series_webid = null;		// xcar车系webid
			String x_series_name = null;		// xcar车系name
			
			String b_brand_webid = null;		// bitauto主品牌webid
			String b_subbrand_webid = null;		// bitauto子品牌webid
			String b_series_webid = null;		// bitauto车系webid
			String b_series_name = null;		// bitauto车系name
			for(Cell cell:row){
				
				//主品牌web_id
				if(cell.getColumnIndex() == 0){
					a_brand_webid = cell.toString();
				}
				//主品牌name
				if(cell.getColumnIndex() == 1){
					a_brand_name = cell.toString();
				}
				//子品牌webid
				if(cell.getColumnIndex() == 2){
					a_subbrand_webid = cell.toString();
				}
				//子品牌name
				if(cell.getColumnIndex() == 3){
					a_subbrand_name = cell.toString();
				}
				//汽车之家车系webid
				if(cell.getColumnIndex() == 4){
					a_series_webid = cell.toString();
				}
				//汽车之家车系名
				if(cell.getColumnIndex() == 5){
					a_series_name = cell.toString();
				}
				//易车子品牌webid
				if(cell.getColumnIndex() == 15){
					b_subbrand_webid = cell.toString();
				}
				//易车车系web_id
				if(cell.getColumnIndex() == 17){
					b_series_webid = cell.toString();
				}
				//易车车系名
				if(cell.getColumnIndex() ==18){
					b_series_name = cell.toString();
				}
				
				
				//爱卡子品牌webid
				if(cell.getColumnIndex() == 8){
					x_subbrand_webid = cell.toString();
				}				
				//爱卡车系web_id
				if(cell.getColumnIndex() == 10){
					x_series_webid = cell.toString();
				}
				//爱车车系名
				if(cell.getColumnIndex() == 11){
					x_series_name = cell.toString();
				}
				
			}
			
//			ResultSet resultSet = stmt.executeQuery("select web_id from t_bitauto_brand as brand,t_bitauto_subbrand as subbrand " +
//					"where brand.web_id=subbrand.brand_web_id and brand.name='" +a_brand_name+
//					"' and subbrand.name='"+a_subbrand_name+"'");
//			
//			while(resultSet.next()){
//				//获取bitauto子品牌id
//				b_subbrand_webid = resultSet.getString(1);
//			}
	
			if(a_series_name != null && a_subbrand_webid != ""){
				PreparedStatement pstate = conn.prepareStatement("insert into t_autohome_vel_series (web_id,name,`desc`,sub_brand_web_id) values(?,?,?,?)");
				
				pstate.setString(1, a_series_webid);
				pstate.setString(2, a_series_name);
				pstate.setString(3, a_desc);
				pstate.setString(4, a_subbrand_webid); 
				pstate.execute();
			}
			
				
		}
		
		
		//删除新老的数据
		stmt.execute("delete from t_autohome_vel_series where id<"+flag);
		stmt.execute("CREATE TEMPORARY TABLE tmp_table_autohome select min(series2.id) from t_autohome_vel_series as series2 GROUP BY series2.web_id;");
		stmt.execute("delete from t_xcar_vel_series  where id not in (select * from tmp_table_autohome);");	

			
	}
}
